package cn.bytecollege.dao;

import cn.bytecollege.entity.Room;

import java.util.List;

public class RoomDao extends BaseDao<Room,Integer>{
    /**
     * @author LH
     * 通过roomNumber找未入住的room
     * @param roomNumber
     * @return
     */
    public Room findByRoomNumber(Integer roomNumber){
        String sql="SELECT room_id,room_category_id,room_price  FROM room WHERE delete_flag=0 and room_state=0 and room_number=?";
        List<Room> list= helper.executeQuery(sql,Room.class,roomNumber);
        Room room=null;
        if (list.size()>0){
            room=list.get(0);
        }
        return room;
    }

    /**
     * @author LH
     * 更改房间状态为1
     * @param roomId
     * @return
     */
    public int updateRoomStateTo1(Integer roomId) {
        String sql="UPDATE room SET room_state=? WHERE room_id = ?";
        Object[] objects={1,roomId};
        return helper.executeUpdate(sql,objects);
    }
    /**
     * @author LH
     * 更改房间状态为0
     * @param roomId
     * @return
     */
    public int updateRoomStateTo0(Integer roomId) {
        String sql="UPDATE room SET room_state=? WHERE room_id = ?";
        Object[] objects={0,roomId};
        return helper.executeUpdate(sql,objects);
    }
    /**
     * 根据状态分页查询所有房间的信息
     * @param pageIndex
     * @param pageSize
     * @param objects
     * @return
     * @author Mr.H
     */
    public List<Room> findByStatePage(int pageIndex, int pageSize, Object... objects) {
        String sql="select r.room_id,r.room_number,rc.room_category_name,r.room_state,r.room_price from room r inner join room_category rc " +
                "on r.room_category_id=rc.room_category_id where r.delete_flag=? and r.room_state=? limit ?,?";
        Room room=(Room) objects[0];
        int index=(pageIndex-1)*pageSize;
        Object[] sqlObjects={room.getDeleteFlag(),room.getRoomState(),index,pageSize};
        return helper.executeQuery(sql,Room.class,sqlObjects);
    }
    /**
     * 查询所有未删除的房间信息
     * @param pageIndex
     * @param pageSize
     * @param objects
     * @return
     * @author Mr.H
     */
    public List<Room> findAllByPage(int pageIndex, int pageSize, Object... objects) {
        String sql="select r.room_id,r.room_number,rc.room_category_name,r.room_state,r.room_price from room r inner join room_category rc " +
                "on r.room_category_id=rc.room_category_id where r.delete_flag=? limit ?,?";
        int index=(pageIndex-1)*pageSize;
        Object[] sqlObjects={0,index,pageSize};
        return helper.executeQuery(sql,Room.class,sqlObjects);
    }

    /**
     * 统计现在还在使用的所有房间数
     * @return
     * @author Mr.H
     */
    public int count(){
        String sql="select count(*) from room where delete_flag=0";
        return helper.executeScale(sql);
    }

    /**
     * 根据房间编号查询房间信息
     * @param number
     * @return
     * @author Mr.H
     */
    public List<Room> findRoomByRoomNumber(Integer number) {
        String sql="select r.room_number,r.room_id,rc.room_category_name,r.room_state,r.room_price from" +
                " room r inner join room_category rc on r.room_category_id=rc.room_category_id and r.delete_flag=? and " +
                "r.room_number=?";
        Object[] objects={0,number};
        return helper.executeQuery(sql,Room.class,objects);
    }

    /**
     * 根据RoomCategoryId查询，房间表中是否有引用该分类的房间
     * @param roomCategoryId
     * @return
     *@author Mr.H
     */
    public List<Room> findRoomCategoryId(Integer roomCategoryId){
        String sql="select r.room_id,r.room_number,r.room_price,r.room_state from room r left join room_category rc on" +
                " r.room_category_id=rc.room_category_id and r.room_state=1 and r.delete_flag=0  where r.room_category_id=?";
        return helper.executeQuery(sql,Room.class,roomCategoryId);
    }
    /**
     * 新增房间
     * YL
     * @param room
     * @return
     */
    @Override
    public int save(Room room) {
        String sql = "insert into room (room_id,room_number,room_category_id, room_price,room_state,delete_flag) VALUES(?,?,?,?,?,?)";
        Object[] objects = new Object[]{room.getRoomId(), room.getRoomNumber(), room.getRoomCategoryId(), room.getRoomPrice(), room.getRoomState(), room.getDeleteFlag()};
        return this.helper.executeUpdate(sql, objects);
    }

    /**
     * 逻辑删除数据
     * YL
     * @param id
     * @return
     */
    public int deleteById(Integer id) {
        String sql = "UPDATE ROOM SET DELETE_FLAG = 1 WHERE ROOM_ID = ? ";
        Object[] objects = new Object[]{id};
        return this.helper.executeUpdate(sql, objects);
    }

    /**
     * 根据ID查询数据
     * YL
     * @param id
     * @return
     */
    public Room findById(Integer id) {
        String sql = "SELECT ROOM_ID,ROOM_NUMBER,ROOM_CATEGORY_ID,ROOM_PRICE,ROOM_STATE, DELETE_FLAG FROM ROOM WHERE ROOM_ID = ?";
        List<Room> list = this.helper.executeQuery(sql, Room.class, new Object[]{id});
        Room room = null;
        if (list.size() > 0) {
            room = (Room) list.get(0);
        }
        return room;
    }

    /**
     * 更新房间
     * YL
     * @param room
     * @return
     */
    public int update(Room room) {
        String sql = "UPDATE ROOM SET ROOM_NUMBER = ?,ROOM_CATEGORY_ID = ?,ROOM_PRICE = ?, ROOM_STATE = ? WHERE ROOM_ID = ?";
        Object[] objects = new Object[]{room.getRoomNumber(), room.getRoomCategoryId(), room.getRoomPrice(), room.getRoomState(), room.getRoomId()};
        return this.helper.executeUpdate(sql, objects);
    }

    /**
     * 方法重载，统计所有已入住的房间数
     * @param flag
     * @return
     */
    public int count(int flag){
        String sql="select count(*) from room where delete_flag=0 and room_state=?";
        return helper.executeScale(sql,flag);
    }
}
